#include "goodinfodao.h"
#include "goodinfo.h"
#include "iostream"
#include "vector"
#include <QSqlDatabase>
#include <QSqlQuery>
#include "exception"
#include "QVariant"
#include "QSqlError"
#include <QApplication>
#include <QFile>
#include <QDir>
#include <QDebug>
#include <map>
#include <QVariantList>
#include <QVariant>

/*
 * 文章备注： 数据库加解密方法以及指定位置生成数据库
1、创建空的sqlite数据库。
//数据库名的后缀你可以直接指定，甚至没有后缀都可以
//方法一：创建一个空sqlite数据库，用IO的方式
FileStream fs = File.Create(“c:\\test.db“);
//方法二：用SQLiteConnection
SQLiteConnection.CreateFile(“c:\\test.db“);

创建的数据库是个0字节的文件。
2、创建加密的空sqlite数据库
//创建一个密码为password的空的sqlite数据库
SQLiteConnection.CreateFile（“c:\\test2.db“);
SQLiteConnection cnn = new SQLiteConnection(“Data Source=c:\\test2.db“);
SQLiteConnection cnn = new SQLiteConnection(“Data Source=D:\\test2.db“);
cnn.Open();
cnn.ChangePassword(“password“);

3、给未加密的数据库加密
SQLiteConnection cnn = new SQLiteConnection(“Data Source=c:\\test.db“);
cnn.Open();
cnn.ChangePassword(“password“);

4、打开加密sqlite数据库
//方法一
SQLiteConnection cnn = new SQLiteConnection(“Data Source=c:\\test2.db“);
cnn.SetPassword(“password“);
cnn.Open();
//方法二
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.DataSource = @”c:\test.db“;
builder.Password = @”password“;
SQLiteConnection cnn = new SQLiteConnection(builder.ConnectionString);
cnn .Open();
*/

GoodInfoDao* GoodInfoDao::instance = NULL;



GoodInfoDao::GoodInfoDao() :
	m_db(NULL),
	m_db_query(NULL),
	m_db_table_name("GoodInfos"),
	m_user_name("Admintor"),
    m_db_path(QString("%1/DB/GoodDB.db").arg(QString("."))), //arg(QApplication::applicationDirPath())), setDatabaseName 方法添加的路徑
    m_user_pw("YQC18924267879")
{
    qDebug() << m_db_path ;
}

GoodInfoDao::~GoodInfoDao()
{
    if(m_db_query != NULL && m_db_query->isActive())
    {
        m_db_query->clear();
        m_db_query = NULL;
    }

    if(m_db != NULL && m_db->isOpen())
    {
        m_db->close();
        m_db = NULL;
    }
}

GoodInfoDao *GoodInfoDao::GetInstance()
{
    if(GoodInfoDao::instance == NULL)
    {
        GoodInfoDao::instance = new GoodInfoDao();
    }
    return GoodInfoDao::instance;
}

QSqlQuery* GoodInfoDao::GetCleanQuery()
{
    // 查询数据库是否存在，如果存在则获取db，如果不存在就直接创建数据库
    if(m_db == NULL)
    {
        try
        {
            CreateDirIfNotExist();
            if (QSqlDatabase::contains("qt_sql_default_connection"))
            {
               m_db = &(QSqlDatabase::database("qt_sql_default_connection"));
            }
            else
            {
                m_db = &(QSqlDatabase::addDatabase("QSQLITE"));
                m_db->setDatabaseName(m_db_path);
                m_db->setUserName(m_user_name);
                m_db->setPassword(m_user_pw);
            }

            if(m_db->open())
            {
                qDebug() << "Open db " << m_db_path << "sul" ;
            }
            else
            {
                qDebug() << "Open db " << m_db_path << "fail" ;
                return NULL;
            }

            m_db_query = new QSqlQuery(*m_db);

            if(!CheckTableIsExist())
            {
                CreateTable();
            }
        }
        catch (std::exception e)
        {
            qDebug()<<e.what();
        }
    }
    else
    {
//        try
//        {
//            if(!m_db->isOpen())
//            {
//                m_db->open();
//            }
//        }
//        catch(std::exception e)
//        {
//            qDebug()<<e.what();
//        }
    }

    m_db_query->clear();
    return m_db_query;
}

bool GoodInfoDao::CheckTableIsExist()
{
    // sqlite_master 是sqlite里面的自建表
    // 该表里面记录数据库中所有的表的名称
    QString str = QString("select count(*) from sqlite_master where type='table' and name='%1'").arg(m_db_table_name);
    auto query = GetCleanQuery();
    bool is_exist = false;
    if(query->exec(str))
    {
        if(query->next())
        {
            is_exist = query->value(0).toBool();
        }
    }

    qDebug()<<"table is exist: " << is_exist ;

    return is_exist;
}

void GoodInfoDao::CreateTable()
{
    QString sql_cmd = QString("CREATE TABLE \"%1\" (").arg(m_db_table_name);
    sql_cmd.append("\"good_id\"  TEXT(12) NOT NULL,");
    sql_cmd.append("\"good_name\"  TEXT(20) NOT NULL,");
    sql_cmd.append("\"cost_price\"  REAL(6,2) NOT NULL,");
    sql_cmd.append("\"sale_price\"  REAL(6,2) NOT NULL,");
    sql_cmd.append("\"inventory\"  INTEGER(6) NOT NULL,");
    sql_cmd.append("\"img_name\"  TEXT(30),");
    sql_cmd.append("\"description\"  TEXT(255),");
    sql_cmd.append("PRIMARY KEY (\"good_id\" ASC)");
    sql_cmd.append(");");

    try
    {
        auto query = GetCleanQuery();
        bool is_suc = query->exec(sql_cmd);
        if(is_suc)
        {
            qDebug()<<"create table suc" ;
        }
        else
        {
            qDebug()<<"create table fail" ;
        }
    }
    catch(std::exception e)
    {
        qDebug()<<e.what() ;
    }
}

void GoodInfoDao::CreateDirIfNotExist()
{
    QString dir_path = QDir::currentPath();
    dir_path.append("/DB");

    qDebug()<<":: GoodInfoDao::CreateTable  "<<dir_path ;

    QDir dir(dir_path);
    if(!dir.exists())
    {
       if(dir.mkdir(dir_path))
       {
           qDebug()<<"Create Dir " ;
       };
    }
}

// search_id 如要自己添加正则表达式
std::vector<GoodInfo> GoodInfoDao::Select(QString search_id)
{
    qDebug()<<"GoodInfoDao::Select()";
    std::vector<GoodInfo> list;
/*  查询语句有问题，暂时屏蔽 */
    //QString sql = QString("SELECT * FROM %1 WHERE \"good_id\" = \"%2\"").arg(m_db_table_name,search_id);
    QString sql = QString::fromLocal8Bit("SELECT * FROM %1 WHERE \"good_id\" LIKE \"%2%\"").arg(m_db_table_name,search_id);
    //QString sql = QString::fromLocal8Bit("select * from %1 where \"good_id\" like \"%2%\"").arg(m_db_table_name,search_id);
    //select * from %1 where good_id like '%%1%'"
    auto query = GetCleanQuery();
    qDebug()<<sql;

    if(query->exec(sql))
    {
        qDebug()<<QString("in if size: %1").arg(QString::number(query->size()));

        while(query->next())
        {

            GoodInfo *good_info = new GoodInfo();
            good_info->good_id = query->value(0).toString();
            good_info->good_name = query->value(1).toString();
            good_info->cost_price = query->value(2).toDouble();
            good_info->sale_price = query->value(3).toDouble();
            good_info->inventory = query->value(4).toInt();
            good_info->img_name = query->value(5).toString();
            good_info->description = query->value(6).toString();

            list.push_back(*good_info);
        }
    }
//*/
//    auto table = ReadFullTableData();
//    qDebug() << QString("  table size : %1" ).arg(QString::number(table.size()));

//    for(GoodInfo info : table)
//    {
//        if(info.good_id.startsWith(search_id))
//        {
//            qDebug() << QString(" good_id : %1 " ).arg(info.good_id);
//            GoodInfo *good_info = new GoodInfo();
//            good_info->good_id     = info.good_id;
//            good_info->good_name   = info.good_name;
//            good_info->cost_price  = info.cost_price;
//            good_info->sale_price  = info.sale_price;
//            good_info->inventory   = info.inventory;
//            good_info->img_name    = info.img_name;
//            good_info->description = info.description;

//            list.push_back(*good_info);
//        }
//    }

    qDebug() << QString("  list size : %1" ).arg(QString::number(list.size()));

    return list;
}

void GoodInfoDao::Insert(GoodInfo good_info)
{
    QString sql = QString("INSERT INTO %1 VALUES(?, ?, ?, ?, ?, ?, ?)").arg(m_db_table_name);
    auto query = GetCleanQuery();
    query->prepare(sql);
    query->addBindValue(good_info.good_id);
    query->addBindValue(good_info.good_name);
    query->addBindValue(good_info.cost_price);
    query->addBindValue(good_info.sale_price);
    query->addBindValue(good_info.inventory);
    query->addBindValue(good_info.img_name);
    query->addBindValue(good_info.description);
    query->exec();
}

// 数据插入
void GoodInfoDao::Insert(std::vector<GoodInfo> good_infos)
{
    // 获取query，并且插入数据都进行批量插入操作
    // 批量插入操作 8000 条数据为一组
    
    try
    {   

//		m_db->transaction();

//		auto query = GetCleanQuery();
//        QString sql = QString("INSERT INTO %1 VALUES(?,?,?,?,?,?,?)").arg(m_db_table_name);
//        query->prepare(sql);

//        std::map<QString, QVariantList > good_info_map;
//        good_info_map.clear();


//		int i = 0;
//        foreach(GoodInfo item , good_infos)
//        {
//            good_info_map["good_id"].push_back(QVariant(item.good_id));
//            good_info_map["good_name"].push_back(QVariant(item.good_name));
//            good_info_map["cost_price"].push_back(QVariant(item.cost_price));
//            good_info_map["sale_price"].push_back(QVariant(item.sale_price));
//            good_info_map["inventory"].push_back(QVariant(item.inventory));
//            good_info_map["img_name"].push_back(QVariant(item.img_name));
//            good_info_map["description"].push_back(QVariant(item.description));

//            ++i;

//            // 8000 条记录提交一次。
////            if(i >= 8000)
////            {
////                i = 0;
////                this->BatchInsert(good_info_map,query);
////                query->execBatch();
////                m_db->commit();
////                good_info_map.clear();
////            }
//        }
//        if(i > 0)
//        {
//            this->BatchInsert(good_info_map,query);
//            //query->execBatch();
//            query->exec();
//            m_db->commit();
//        }

//        std::map<QString, QVariantList > good_info_map;
//        good_info_map.clear();

        qDebug() << QString(" insert size: %1").arg(QString::number(good_infos.size()));

        // 下面的批处理无法使用，暂时使用这种方式插入数据
        for(GoodInfo item : good_infos)
        {
            this->Insert(item);
        }
    }
    catch(std::exception e)
    {
        // m_db->rollback();
        qDebug() << e.what() ;
    }
}

void GoodInfoDao::BatchInsert(std::map<QString, QVariantList > good_info_map, QSqlQuery* query)
{
    query->addBindValue(good_info_map["good_id"]);
    query->addBindValue(good_info_map["good_name"]);
    query->addBindValue(good_info_map["cost_price"]);
    query->addBindValue(good_info_map["sale_price"]);
    query->addBindValue(good_info_map["inventory"]);
    query->addBindValue(good_info_map["img_name"]);
    query->addBindValue(good_info_map["description"]);
}

bool GoodInfoDao::Update(GoodInfo good_info)
{
    QString sql = QString("UPDATE %1 ").arg(m_db_table_name);
    sql.append("SET ");
    sql.append(QString("good_name = \"%2\",").arg(good_info.good_name));
    sql.append(QString("cost_price = %3,").arg(QString::number(good_info.cost_price)));
    sql.append(QString("sale_price = %4,").arg(QString::number(good_info.sale_price)));
    sql.append(QString("inventory = %5,").arg(QString::number(good_info.inventory)));
    sql.append(QString("img_name = \"%6\",").arg(good_info.img_name));
    sql.append(QString("description = \"%7\"").arg(good_info.description));
    sql.append(QString(" WHERE good_id = \"%8\"").arg(good_info.good_id));
    qDebug() << QString(" GoodInfoDao::Update %1").arg(sql);
    auto query = GetCleanQuery();
    return query->exec(sql);
}

void GoodInfoDao::Delete(GoodInfo good_info)
{
    QString sql = QString("DELETE from %1 WHERE good_id = \"%2\"").arg(m_db_table_name,good_info.good_id);
    auto query = GetCleanQuery();
    ExecQuery(query,sql);
}

std::vector<GoodInfo> GoodInfoDao::ReadFullTableData()
{
    qDebug()<<"GoodInfoDao::Select()";

    std::vector<GoodInfo> list;
    QString sql = QString("SELECT * From %1").arg(m_db_table_name);
    auto query = GetCleanQuery();
    query->prepare(sql);

    if(query->exec())
    {
        while(query->next())
        {
            GoodInfo good_info;
            good_info.good_id = query->value(0).toString();
            good_info.good_name = query->value(1).toString();
            good_info.cost_price = query->value(2).toDouble();
            good_info.sale_price = query->value(3).toDouble();
            good_info.inventory = query->value(4).toInt();
            good_info.img_name = query->value(5).toString();
            good_info.description = query->value(6).toString();

            list.push_back(good_info);
        }
    }
    return list;
}

void GoodInfoDao::ExecQuery(QSqlQuery *query, QString meg)
{
    if(query->exec(meg))
    {
        qDebug()<<"ExecQuery Is sul " << meg ;
    }
    else
    {
        qDebug()<<query->lastError().text();
    }
}

